<?php
class ModelAccountOrder extends Model {
    const ORDER_STATUS_ID_UNPAID = 0;
    protected $table = 'order';

    public function getOrder($order_id) {
//		$order_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order` WHERE order_id = '" . (int)$order_id . "' AND customer_id = '" . (int)$this->customer->getId() . "' AND order_status_id > '0'");
        // 修改成显示未支付的订单
        $sql = "SELECT * FROM `" . DB_PREFIX . "order` WHERE order_id = '" . (int) $order_id . "' AND customer_id = '" . (int) $this->customer->getId(). "'";
        $order_query = $this->db->query($sql);

		if ($order_query->num_rows) {
			$country_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "country` WHERE country_id = '" . (int)$order_query->row['payment_country_id'] . "'");

			if ($country_query->num_rows) {
				$payment_iso_code_2 = $country_query->row['iso_code_2'];
				$payment_iso_code_3 = $country_query->row['iso_code_3'];
			} else {
				$payment_iso_code_2 = '';
				$payment_iso_code_3 = '';
			}

			$zone_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "zone` WHERE zone_id = '" . (int)$order_query->row['payment_zone_id'] . "'");

			if ($zone_query->num_rows) {
				$payment_zone_code = $zone_query->row['code'];
			} else {
				$payment_zone_code = '';
			}

			$country_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "country` WHERE country_id = '" . (int)$order_query->row['shipping_country_id'] . "'");

			if ($country_query->num_rows) {
				$shipping_iso_code_2 = $country_query->row['iso_code_2'];
				$shipping_iso_code_3 = $country_query->row['iso_code_3'];
			} else {
				$shipping_iso_code_2 = '';
				$shipping_iso_code_3 = '';
			}

			$zone_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "zone` WHERE zone_id = '" . (int)$order_query->row['shipping_zone_id'] . "'");

			if ($zone_query->num_rows) {
				$shipping_zone_code = $zone_query->row['code'];
			} else {
				$shipping_zone_code = '';
			}

			return array(
				'order_id'                => $order_query->row['order_id'],
				'invoice_no'              => $order_query->row['invoice_no'],
				'invoice_prefix'          => $order_query->row['invoice_prefix'],
				'store_id'                => $order_query->row['store_id'],
				'store_name'              => $order_query->row['store_name'],
				'store_url'               => $order_query->row['store_url'],
				'customer_id'             => $order_query->row['customer_id'],
				'fullname'               => $order_query->row['fullname'],
				'telephone'               => $order_query->row['telephone'],
				'email'                   => $order_query->row['email'],
				'payment_fullname'       => $order_query->row['payment_fullname'],
                'payment_telephone'       => $order_query->row['payment_telephone'],
				'payment_company'         => $order_query->row['payment_company'],
				'payment_address_1'       => $order_query->row['payment_address_1'],
				'payment_address_2'       => $order_query->row['payment_address_2'],
				'payment_postcode'        => $order_query->row['payment_postcode'],
                'payment_city_id'         => $order_query->row['payment_city_id'],
				'payment_city'            => $order_query->row['payment_city'],
				'payment_zone_id'         => $order_query->row['payment_zone_id'],
				'payment_zone'            => $order_query->row['payment_zone'],
				'payment_zone_code'       => $payment_zone_code,
				'payment_country_id'      => $order_query->row['payment_country_id'],
				'payment_country'         => $order_query->row['payment_country'],
                'payment_county_id'      => $order_query->row['payment_county_id'],
                'payment_county'         => $order_query->row['payment_county'],
				'payment_iso_code_2'      => $payment_iso_code_2,
				'payment_iso_code_3'      => $payment_iso_code_3,
				'payment_address_format'  => $order_query->row['payment_address_format'],
				'payment_method'          => $order_query->row['payment_method'],
				'shipping_fullname'      => $order_query->row['shipping_fullname'],
                'shipping_telephone'      => $order_query->row['shipping_telephone'],
				'shipping_company'        => $order_query->row['shipping_company'],
				'shipping_address_1'      => $order_query->row['shipping_address_1'],
				'shipping_address_2'      => $order_query->row['shipping_address_2'],
				'shipping_postcode'       => $order_query->row['shipping_postcode'],
                'shipping_city_id'        => $order_query->row['shipping_city_id'],
				'shipping_city'           => $order_query->row['shipping_city'],
				'shipping_zone_id'        => $order_query->row['shipping_zone_id'],
				'shipping_zone'           => $order_query->row['shipping_zone'],
				'shipping_zone_code'      => $shipping_zone_code,
				'shipping_country_id'     => $order_query->row['shipping_country_id'],
				'shipping_country'        => $order_query->row['shipping_country'],
                'shipping_county_id'      => $order_query->row['shipping_county_id'],
                'shipping_county'         => $order_query->row['shipping_county'],
				'shipping_iso_code_2'     => $shipping_iso_code_2,
				'shipping_iso_code_3'     => $shipping_iso_code_3,
				'shipping_address_format' => $order_query->row['shipping_address_format'],
				'shipping_method'         => $order_query->row['shipping_method'],
				'shipping_shipper_code'   => $order_query->row['shipping_shipper_code'],
				'shipping_logistic_code'  => $order_query->row['shipping_logistic_code'],
				'comment'                 => $order_query->row['comment'],
				'total'                   => $order_query->row['total'],
				'order_status_id'         => $order_query->row['order_status_id'],
				'language_id'             => $order_query->row['language_id'],
				'currency_id'             => $order_query->row['currency_id'],
				'currency_code'           => $order_query->row['currency_code'],
				'currency_value'          => $order_query->row['currency_value'],
				'date_modified'           => $order_query->row['date_modified'],
				'date_added'              => $order_query->row['date_added'],
				'ip'                      => $order_query->row['ip']
			);
		} else {
			return false;
		}
	}

	public function getOrders($start = 0, $take = 20, $filter_data = []) {
        $start = $start > 0 ? $start : 0;
        $take = $take > 1 ? $take : 1;

        // 未支付过期订单自动删除（有效期：1天）,可以放消息队列中
        $this->deleteOrderIsExpired($this->customer->getId());

//		$query = $this->db->query("SELECT o.order_id, o.fullname, os.name as status, o.date_added, o.total, o.currency_code, o.currency_value FROM `" . DB_PREFIX . "order` o LEFT JOIN " . DB_PREFIX . "order_status os ON (o.order_status_id = os.order_status_id) WHERE o.customer_id = '" . (int)$this->customer->getId() . "' AND o.order_status_id > '0' AND o.store_id = '" . (int)$this->config->get('config_store_id') . "' AND os.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY o.order_id DESC LIMIT " . (int)$start . "," . (int)$take);
		// 已修改成需要包括未支付订单
        $select = "SELECT o.order_id, o.fullname, os.name as status, o.date_added, o.total, o.currency_code, o.currency_value, o.order_status_id, o.shipping_shipper_code, o.shipping_logistic_code FROM `" . DB_PREFIX . "order` o LEFT JOIN " . DB_PREFIX . "order_status os ON (o.order_status_id = os.order_status_id) ";
        if (! empty($filter_data['filter_not_reviewed'])) {
            $select .= $this->notReviewedJoinSql();
        }
		
        $where = "WHERE o.customer_id = '" . (int) $this->customer->getId() . "' AND o.store_id = '" . (int) $this->config->get('config_store_id') . "' AND os.language_id = '" . (int) $this->config->get('config_language_id') . "' ";
        if (! empty($filter_data['filter_not_reviewed'])) {
            $where .= "AND prod_cnt > COALESCE(review_cnt, 0) ";
        }
		if (isset($filter_data['order_status_ids'])) {
		    $status_ids = implode(',', $filter_data['order_status_ids']);
            $where .= "AND o.order_status_id IN ($status_ids) ";
        }
        
		$order_by = "ORDER BY o.order_id DESC ";
		$limit = "LIMIT ". (int) $start . "," . (int) $take;

        $query = $this->db->query($select. $where . $order_by . $limit);

		return $query->rows;
	}

	public function getOrderProduct($order_id, $order_product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "' AND order_product_id = '" . (int)$order_product_id . "'");

		return $query->row;
	}

	public function getOrderProducts($order_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'");

		return $query->rows;
	}

    /**
     * @see \Cart\Cart::getProducts() - TODO 需要参考购物车检查库存等
     *
     * @param $order_id
     *
     * @return mixed
     */
	public function getOrderPayProducts($order_id) {
        $tbl = DB_PREFIX . "order_product";
        $prod_tbl = DB_PREFIX . "product";
        $order_id = (int) $order_id;

        $select = "$tbl.*, $prod_tbl.tax_class_id, $prod_tbl.quantity as stock";

		$query = $this->db->query("SELECT $select FROM $tbl  LEFT JOIN $prod_tbl USING(product_id) WHERE order_id = '$order_id'");

		return $query->rows;
	}

	public function getOrdersProducts($order_ids) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_product WHERE  order_id IN (" . implode(',', $order_ids) . ")");

		return $query->rows;
	}

	public function getOrderOptions($order_id, $order_product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_option WHERE order_id = '" . (int)$order_id . "' AND order_product_id = '" . (int)$order_product_id . "'");

		return $query->rows;
	}

	public function getOrderVouchers($order_id) {
		$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_voucher` WHERE order_id = '" . (int)$order_id . "'");

		return $query->rows;
	}

	public function getOrderTotals($order_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_total WHERE order_id = '" . (int)$order_id . "' ORDER BY sort_order");

		return $query->rows;
	}

	public function getOrderHistories($order_id) {
		$query = $this->db->query("SELECT date_added, os.name AS status, oh.comment, oh.notify FROM " . DB_PREFIX . "order_history oh LEFT JOIN " . DB_PREFIX . "order_status os ON oh.order_status_id = os.order_status_id WHERE oh.order_id = '" . (int)$order_id . "' AND os.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY oh.date_added");

		return $query->rows;
	}

	public function getTotalOrders($filter_data = []) {
//		$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` o WHERE customer_id = '" . (int)$this->customer->getId() . "' AND o.order_status_id > '0' AND o.store_id = '" . (int)$this->config->get('config_store_id') . "'");
        $select = "SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` o ";
        if (! empty($filter_data['filter_not_reviewed'])) {
            $select .= $this->notReviewedJoinSql();
        }
        
        $where = " WHERE customer_id = '" . (int) $this->customer->getId() . "' AND o.store_id = '" . (int) $this->config->get('config_store_id') . "' ";
        if (isset($filter_data['order_status_ids'])) {
            $status_ids = implode(',', $filter_data['order_status_ids']);
            $where .= "AND o.order_status_id IN ($status_ids) ";
        }
        if (! empty($filter_data['filter_not_reviewed'])) {
            $where .= "AND prod_cnt > COALESCE(review_cnt, 0) ";
        }

        $query = $this->db->query($select . $where);
		return $query->row['total'];
	}

	public function getTotalOrderProductsByOrderId($order_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'");

		return $query->row['total'];
	}

	public function getTotalOrderVouchersByOrderId($order_id) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order_voucher` WHERE order_id = '" . (int)$order_id . "'");

		return $query->row['total'];
	}

    public function getOrderStatus($status_id)
    {
        $table = DB_PREFIX . 'order_status';

        $lang_id = (int) $this->config->get('config_language_id');

        $sql = "SELECT * FROM `$table` WHERE order_status_id = '$status_id' AND language_id = '$lang_id'";

        $query = $this->db->query($sql);

        return $query->row;
    }

    public function isStatusComplete($status_id)
    {
        return in_array($status_id, $this->registry->get('config')->get('config_complete_status'));
    }

    public function isStatusProcessing($status_id)
    {
        return in_array($status_id, $this->registry->get('config')->get('config_processing_status'));
    }

    public function delete($order_id)
    {
        $sql = "DELETE FROM `" . DB_PREFIX . "order` WHERE order_id = '" . (int) $order_id . "' ";
        // todo 关联表的清除
        $query = $this->db->query($sql);

        return $query->num_rows;
    }

    /**
     * @return mixed
     */
    public function deleteOrdersUnpaid()
    {
        $due = \Carbon\Carbon::now()->subDay()->toDateTimeString();

        $sql = "DELETE FROM `" . DB_PREFIX . "order` WHERE order_status_id = '0' AND date_added < '$due' ";

        $query = $this->db->query($sql);

        return $query->num_rows;
    }
    
    /**
     * @return string
     */
    private function notReviewedJoinSql(): string
    {
        return <<<JOIN_NOT_REVIEWED
LEFT JOIN (
    SELECT count(*) prod_cnt, order_id, order_product_id
    FROM oc_order_product
    GROUP BY order_id
) order_products ON o.order_id = order_products.order_id
LEFT JOIN (
    SELECT count(*) review_cnt, oc_order_product.order_id
    FROM oc_review
	JOIN oc_order_product ON oc_order_product.order_product_id = oc_review.order_product_id
	GROUP BY order_id
) reviews ON reviews.order_id = o.order_id 

JOIN_NOT_REVIEWED;
    }

    /**
     * @param $order_id
     * @param array $payment - Model Payment
     *
     * @return mixed
     */
    public function patchPayment($order_id, $payment)
    {
        $update = \Carbon\Carbon::now()->toDateTimeString();
        $code = $payment['code'];
        $method = $payment['title'];

        $sql = "UPDATE `" . DB_PREFIX . "order` SET payment_code = '$code', payment_method = '$method', date_modified = '$update' WHERE order_id = '$order_id' ";

        $query = $this->db->query($sql);

        return $query->num_rows;
    }

    private function deleteOrderIsExpired($customer_id)
    {

    }
}